package com.matt.repository;

import com.dotflat.dashboard.vo.ChildrenClassCheckVO;
import com.matt.domain.Children;
import com.matt.domain.Classes;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

/**
 * *******************************
 * Created by Ron.
 * form com.matt
 * on 2017/7/6
 * *******************************
 */
public interface ClassesRepository extends JpaRepository<Classes, String>, JpaSpecificationExecutor<Classes> {

    List<Classes> findByCategoryAndTargetObjectIdAndDeleted(Integer category, String targetObjectId, Boolean deleted);

    List<Classes> findByCategoryAndDeleted(Integer category, Boolean deleted);

    Classes findFirstById(String id);

    Classes findByNameAndAreaId(String name, String areaId);

    List<Classes> findByAreaId(String areaId);

    List<Classes> findByCategoryAndAreaId(Integer category, String areaId);

    List<Classes> findByAreaIdAndDeleted(String areaId, boolean deleted);

    List<Classes> findByAreaIdAndCategoryAndDeleted(String areaId, Integer category, boolean deleted);

    Classes findFirstByAreaIdAndIdAndDeletedIsFalse(String areaId, String id);

    @Query(value = "SELECT c.* from rel_classes_children r left JOIN classes c on r.classesId = c.id where r.childrenId = :childrenId limit 1", nativeQuery = true)
    Classes findByChildrenId(@Param("childrenId") String childrenId);

    @Query(value = "SELECT distinct c.* from rel_classes_children r left JOIN classes c on r.classesId = c.id where r.childrenId = :childrenId", nativeQuery = true)
    List<Classes> findAllByChildrenId(@Param("childrenId") String childrenId);

    @Query(value = "SELECT c.* from rel_classes_teacher r left JOIN classes c on r.classesId = c.id where r.staffId = :staffId",nativeQuery = true)
    List<Classes> findByStaffId(@Param("staffId") String staffId);

    List<Classes> findByInChargeTeacherIdAndAreaIdAndDeleted(String teacherId, String areaId, boolean deleted);

    Long countByAreaIdAndDeleted(String areaId,boolean deleted);

    Long countByAreaIdAndCategoryAndDeleted(String areaId,Integer category,boolean deleted);

    //获取园所班级总数
    @Query(value = "SELECT count(id) FROM  classes where   areaId in (?1)", nativeQuery = true)
    Integer getAreaIdCount( List<String> areaId);

    //获取集团每个园所班级总数
    @Query(value = "SELECT count(id) FROM  classes where   areaId in (?1)  GROUP BY  areaId", nativeQuery = true)
    List<Integer> getAreaIdCountList( List<String> areaId);

    List<Classes> findByIdIn(String[] classIds);

    @Query(value = "select cla.`name` as classesName, chi.`name` as childrenName, re.`status` as `status`, re.checkInTime as checkInTime, re.checkOutTime as checkOutTime,re.childrenId as childrenId,re.temperature as temperature,re.temperature1 as temperature1"+
            " from check_record re " +
            " left join children chi on chi.id = re.childrenId " +
            " left join classes cla on cla.id = re.classesId " +
            " where re.classesId = :classesId " +
            " and re.childrenId is not null " +
            " and TO_DAYS(re.checkInTime) = TO_DAYS(:todayDate)", nativeQuery = true)
    List<Object[]> getClassCheckDetail(@Param("classesId") String classesId, @Param("todayDate") String todayDate);


    @Modifying
    @Transactional
    @Query(value = "update check_record set checkInTime = :checkInTimeCopy,`status` = CASE WHEN :status IS NOT NULL THEN :status ELSE `status` END where DATE(checkInTime) = DATE(:checkInTime) and childrenId = :childrenId", nativeQuery = true)
    int updateCheckInTime(@Param("checkInTimeCopy") String checkInTimeCopy, @Param("status") Integer status, @Param("checkInTime") String checkInTime,@Param("childrenId") String childrenId);

    @Modifying
    @Transactional
    @Query(value = "update check_record set checkOutTime = :checkOutTimeCopy,`status` = CASE WHEN :status IS NOT NULL THEN :status ELSE `status` END where DATE(checkOutTime) = DATE(:checkOutTime) and childrenId = :childrenId", nativeQuery = true)
    int updateCheckOutTime(@Param("checkOutTimeCopy") String checkOutTimeCopy, @Param("status") Integer status, @Param("checkOutTime") String checkOutTime,@Param("childrenId") String childrenId);
}
